#!/bin/bash


#############
# VARIABLES #
#############

# Commands
BOLD=`tput bold`
NORMAL=`tput sgr0`

# Options
DB_NAME=cheliou
DB_USER_NAME=chelioudbdev
DB_USER_PWD=dbDev
LOG_FILE=db-log
PLAYED_SCRIPTS_TABLE_NAME=playedscripts
SCRIPTS_FOLDER=schema-updates
UPDATE_MODE=false
WEB_USER_NAME=cheliouwebdev
WEB_USER_PWD=webDev

##
# Cleans the log file's content.
# @author Clément HELIOU (clement.heliou@gmail.com)
# @since 1.0
##
cleanLogFileContent(){
   echo "" > $LOG_FILE
}

##
# Creates a clean database with the given name ($1) and PostgreSQL user ($2) as owner.
# @author Clément HELIOU (clement.heliou@gmail.com)
# @since 1.0
##
createDatabase(){
   echo "Creating database '$1' with owner '$2'..."
   psqlRoot "CREATE DATABASE $1 WITH ENCODING = 'UTF-8' OWNER=$2 CONNECTION LIMIT=-1" &>> $LOG_FILE && echo "Database '$1' created."
}

##
# Creates a PostgreSQL user from the given name ($1) and password ($2).
# @author Clément HELIOU (clement.heliou@gmail.com)
# @since 1.0
##
createPostgreSQLUser(){
   echo "Creating user '$1'..."
   psqlRoot "CREATE USER $1 WITH PASSWORD '$2' CREATEDB" &>> $LOG_FILE && echo "User '$1' created."
}

##
# Creates a sequence with the given name ($1) and grants roles to the project users.
# @author Clément HELIOU (clement.heliou@gmail.com)
# @since 1.0
##
createSequence(){
   echo "Creating '$1' sequence..."
   psqlDbUserCommand "CREATE SEQUENCE $1 INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1" &>> $LOG_FILE 
   psqlDbUserCommand "ALTER TABLE $1 OWNER TO $DB_USER_NAME" &>> $LOG_FILE
   psqlDbUserCommand "GRANT ALL ON TABLE $1 TO $DB_USER_NAME" &>> $LOG_FILE
   psqlDbUserCommand "GRANT SELECT, UPDATE ON TABLE $1 TO $WEB_USER_NAME" &>> $LOG_FILE
   echo "'$1' sequence created."
}

##
# Creates the table containing the played scripts and its sequence.
# @author Clément HELIOU (clement.heliou@gmail.com)
# @since 1.0
##
createSequenceAndTablePlayedScripts(){
   SEQUENCE_NAME="seq_$PLAYED_SCRIPTS_TABLE_NAME"
   createSequence $SEQUENCE_NAME

   echo "Creating '$PLAYED_SCRIPTS_TABLE_NAME' table..."
   psqlDbUserCommand "CREATE TABLE $PLAYED_SCRIPTS_TABLE_NAME (id NUMERIC NOT NULL DEFAULT nextval('$SEQUENCE_NAME'::regclass), name TEXT NOT NULL, launchingdate TIMESTAMP WITHOUT TIME ZONE NOT NULL) WITH (OIDS=false)" &>> $LOG_FILE
   psqlDbUserCommand "ALTER TABLE $PLAYED_SCRIPTS_TABLE_NAME OWNER TO $DB_USER_NAME" &>> $LOG_FILE
   psqlDbUserCommand "GRANT ALL ON TABLE $PLAYED_SCRIPTS_TABLE_NAME TO $DB_USER_NAME" &>> $LOG_FILE
   psqlDbUserCommand "GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE $PLAYED_SCRIPTS_TABLE_NAME TO $WEB_USER_NAME" &>> $LOG_FILE
   echo "'$PLAYED_SCRIPTS_TABLE_NAME' table created."
}

##
# Displays the selected options. This function is only relevant if you call
# it after the script's parameters parsing.
# @author Clément HELIOU (clement.heliou@gmail.com)
# @since 1.0
##
displaySelectedOptions(){

   # 1. Mode
   if $UPDATE_MODE; then
      echo "" && echo "${BOLD}Update${NORMAL} mode enabled"
   else
      echo "" && echo "${BOLD}Creation${NORMAL} mode enabled"
   fi

   # 2. Scripts folder
   echo "${BOLD}Scripts folder:${NORMAL} $SCRIPTS_FOLDER"
}

##
# Drops the database ($1) if exists.
# @author Clément HELIOU (clement.heliou@gmail.com)
# @since 1.0
##
dropExistingDatabase(){
   psqlRoot "DROP DATABASE IF EXISTS $1" &>> $LOG_FILE && echo "Database '$1' is deleted."
}

##
# Drops the existing PostgreSQL users for the project if they exist.
# Then, creates new ones.
# @author Clément HELIOU (clement.heliou@gmail.com)
# @since 1.0
##
dropExistingPostgreSQLUsersAndCreateNewOnes(){

   dropPostgreSQLUserIfExists $DB_USER_NAME
   echo "" && createPostgreSQLUser $DB_USER_NAME $DB_USER_PWD

   echo "" && dropPostgreSQLUserIfExists $WEB_USER_NAME
   echo "" && createPostgreSQLUser $WEB_USER_NAME $WEB_USER_PWD
}

##
# Drops the given PostgreSQL user ($1) if exists.
# @author Clément HELIOU (clement.heliou@gmail.com)
# @since 1.0
##
dropPostgreSQLUserIfExists(){
   psqlRoot "DROP USER IF EXISTS $1" &>> $LOG_FILE && echo "User '$1' is deleted."
}

##
# Logs the given parameter as an output title.
# @author Clément HELIOU (clement.heliou@gmail.com)
# @since 1.0
##
logTitle(){
   echo ""
   echo "===================================="
   echo "=> $1"
   echo "===================================="
}

##
# Moves to the scripts folder and exits if this one does not exist
# @author Clément HELIOU (clement.heliou@gmail.com)
# @since 1.0
##
moveToScriptsFolder(){
   cd $SCRIPTS_FOLDER
   if [ "$?" != "0" ] ; then
      exit "1"
   fi
}

##
# Parses the script's parameters and sets the variables with their content.
# @author Clément HELIOU (clement.heliou@gmail.com)
# @since 1.0
##
parseOptions(){
   for param in "$@" 
   do
        case $param in 

	   --folder)
           SCRIPTS_FOLDER=$2
	   shift
     	   shift
	   ;;

	   --update)
	   UPDATE_MODE=true
	   shift
           ;;
	esac
   done
}

##
# Runs the given PostgreSQL command line ($1) with the database user.
# @author Clément HELIOU (clement.heliou@gmail.com)
# @since 1.0
##
psqlDbUserCommand(){
   psql -U $DB_USER_NAME -d $DB_NAME -c "$1"
}

##
# Runs the given SQL file ($1) with the database user.
# @author Clément HELIOU (clement.heliou@gmail.com)
# @since 1.0
##
psqlDbUserFile(){
   psql -U $DB_USER_NAME -d $DB_NAME -f "$1"
}


##
# Runs the given PostgreSQL command line ($1) with the super user.
# @author Clément HELIOU (clement.heliou@gmail.com)
# @since 1.0
##
psqlRoot(){
   psql -U postgres -d postgres -c "$1"
}

##
# Runs the SQL files contained in the current folder if they had not yet been played.
# @author Clément HELIOU (clement.heliou@gmail.com)
# @since 1.0
##
runSQLScripts(){
   for i in `ls -N *.sql`
   do
     fullScriptPath=`readlink -f $i`
     psqlDbUserCommand "SELECT EXISTS(SELECT id FROM playedscripts WHERE name='$fullScriptPath')" 2>> $LOG_FILE | grep -o -q t

     if [ "$?" != "0" ] ; then
       echo "(+) $i" && psqlDbUserFile $fullScriptPath &>> $LOG_FILE
       if [ "$?" == "0" ] ; then
          psqlDbUserCommand "INSERT INTO playedscripts (name, launchingdate) VALUES ('$fullScriptPath',now())" &>> $LOG_FILE
       fi
     else
       echo $i
     fi
   done
}

#########
# BEGIN #
#########

# 1. Parse the script's parameters
parseOptions $@

# 2. Clean the log file's content & display the selected options
cleanLogFileContent
logTitle "OPTIONS" && displaySelectedOptions

# 3. Create PostgreSQL users and database if creation mode enabled
if ! $UPDATE_MODE; then
   
   export PGPASSWORD=postgres

   logTitle "DATABASE DELETION" && echo "" && dropExistingDatabase $DB_NAME
   logTitle "USERS" && echo "" && dropExistingPostgreSQLUsersAndCreateNewOnes
   logTitle "DATABASE CREATION" && echo "" && createDatabase $DB_NAME $DB_USER_NAME

   export PGPASSWORD="$DB_USER_PWD"
   createSequenceAndTablePlayedScripts

   export PGPASSWORD=
fi

# 4. Move to the scripts folder
logTitle "SCHEMA UPDATES" && echo "" && moveToScriptsFolder

export PGPASSWORD="$DB_USER_PWD"
runSQLScripts
export PGPASSWORD=

echo ""

